/******************************************************************************
** File		: Permission.sql
** Description	: Script for Setting Roles & Permissions from Stored Procedures, functions.
** Params	: None
** Returns	: None
** Author	: Randy Evans
** Date		: 08/10/2009	
** ****************************************************************************/

	SET NOCOUNT ON
		IF NOT EXISTS (SELECT NAME FROM SYSUSERS WHERE NAME = 'ISRMExecuteObjects')
		BEGIN
			EXEC SP_ADDROLE [ISRMExecuteObjects] 
		END
GO

----------------------------------------------------------------------------------------
-- Execution Permission for Stored procedures 
----------------------------------------------------------------------------------------

	GRANT EXECUTE ON dbo.[AuthZ_spDeleteAllUsersForRole] TO [ISRMExecuteObjects]
	GRANT EXECUTE ON dbo.[AuthZ_spGetChildRoles] TO [ISRMExecuteObjects]
	GRANT EXECUTE ON dbo.[AuthZ_spGetGroupsByRoles] TO [ISRMExecuteObjects]
	GRANT EXECUTE ON dbo.[AuthZ_spGetParentRoles] TO [ISRMExecuteObjects]
	GRANT EXECUTE ON dbo.[AuthZ_spGetPeople] TO [ISRMExecuteObjects]
	GRANT EXECUTE ON dbo.[AuthZ_spGetRolesByGroup] TO [ISRMExecuteObjects]
	GRANT EXECUTE ON dbo.[AuthZ_spGetRolesByPersonnelNumber] TO [ISRMExecuteObjects]
	GRANT EXECUTE ON dbo.[AuthZ_spGetRolesByUser] TO [ISRMExecuteObjects]
	GRANT EXECUTE ON dbo.[AuthZ_spGetUserByAlias] TO [ISRMExecuteObjects]
	GRANT EXECUTE ON dbo.[AuthZ_spGetUsersByRole] TO [ISRMExecuteObjects]
	GRANT EXECUTE ON dbo.[AuthZ_spInsertUserToRole] TO [ISRMExecuteObjects]
	GRANT EXECUTE ON dbo.[AuthZ_spIsInRole] TO [ISRMExecuteObjects]
	GRANT EXECUTE ON dbo.[AuthZ_spUpdateAuthZUserRole] TO [ISRMExecuteObjects]

GO
	
	----------------------------------------------------------------------------------------
	--  Select Permission for Tables
	----------------------------------------------------------------------------------------
	
	GRANT SELECT ON dbo.[AuthorizationGroup] TO [ISRMExecuteObjects]
	GRANT SELECT ON dbo.[AuthZUser] TO [ISRMExecuteObjects]
	GRANT SELECT ON dbo.[AuthZUserRole] TO [ISRMExecuteObjects]
	GRANT SELECT ON dbo.[AuthZUserRoleMapping] TO [ISRMExecuteObjects]
	GRANT SELECT ON dbo.[Role] TO [ISRMExecuteObjects]
	GRANT SELECT ON dbo.[RoleMapping] TO [ISRMExecuteObjects]
GO


----------------------------------------------------
--Select Permission for View
----------------------------------------------------
GRANT SELECT ON dbo.[AuthZ_vwUserRole] TO [ISRMExecuteObjects]

GO
Print 'Setting Permission for Objects Process has Completed'